﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Collections;
using System.Data.SqlClient;

namespace AlgoLogicMessenger
{
    //Interaction logic for Report.xaml
    public partial class Report : Page
    {
        public Report()
        {
            InitializeComponent();
            pieChart.Visibility = Visibility.Hidden;
            barChart.Visibility = Visibility.Hidden;
            columnChart.Visibility = Visibility.Hidden;
            try
            {
                SqlConnection conn = new SqlConnection(connstr);
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = new SqlCommand("SELECT DISTINCT Date FROM Result", conn);
                SqlDataReader dr = da.SelectCommand.ExecuteReader();
                string d = "";
                string q = "";
                DateTime dt;
                while (dr.Read())
                {
                    d = dr["Date"].ToString();
                    dt = Convert.ToDateTime(d);
                    string item = dt.ToShortDateString();
                    comboBox1.Items.Add(item);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            comboBox2.Items.Add("PieChart");
            comboBox2.Items.Add("BarChart");
            comboBox2.Items.Add("ColumnChart");
        }

        //string connstr = @"Data Source=.;Initial Catalog=AlgoLogicDatabase;Persist Security Info=True;User ID = sa; Password = imsa";
        string connstr = @"Data Source=.;Initial Catalog=AlgoLogicDB;Integrated Security =True";
        string teamName = null;
        int score = 0;

        List<KeyValuePair<string, int>> valueList = new List<KeyValuePair<string, int>>();

        // compare the value in the list
        static int Compare(KeyValuePair<string, int> a, KeyValuePair<string, int> b)
        {
            return a.Value.CompareTo(b.Value);
        }

        private void showColumnChart()
        {
            if (valueList.Count > 0)
            {
                valueList.Clear();
            }
            string itemsel = comboBox1.SelectedItem.ToString();
            //string[] splitcolon = itemsel.Split(';');

            //string connstr = @"Data Source=.;Initial Catalog=AlgoLogicDatabase;Persist Security Info=True;User ID = sa; Password = imsa";
            string connstr = @"Data Source=.;Initial Catalog=AlgoLogicDB;Integrated Security =True";
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter();
            //da.SelectCommand = new SqlCommand("SELECT distinct TeamName, SUM(Score) AS score FROM Result GROUP BY TeamName", conn);
            da.SelectCommand = new SqlCommand("SELECT distinct r.TeamName, SUM(r.Score)AS scor FROM (SELECT Result.QuizNum,Result.TeamName,Result.Score,CAST(CAST([Date] AS DATE) AS DATETIME) + CAST([Time] AS TIME) AS dt FROM dbo.Result," +
                                              "(SELECT QuizNum,MIN(CAST(CAST([Date] AS DATE) AS DATETIME) + CAST([Time] AS TIME)) AS fastest FROM dbo.Result WHERE Score = 1 AND Date=@date GROUP BY QuizNum) AS t WHERE Result.QuizNum = t.QuizNum" +
                                              " AND CAST(CAST([Date] AS DATE) AS DATETIME) + CAST([Time] AS TIME) = t.fastest) AS r GROUP BY r.TeamName", conn);
            da.SelectCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(itemsel));
            SqlDataReader dr = da.SelectCommand.ExecuteReader();
            ArrayList teamming = new ArrayList();
            while (dr.Read())
            {
                teamName = dr["TeamName"].ToString();
                score = Convert.ToInt32(dr["scor"]);

                valueList.Add(new KeyValuePair<string, int>(teamName, score));
                teamming.Add(teamName);

            }
            conn.Close();

            //CODE CHANGE(COPY FROM HERE)
            SqlConnection conn1 = new SqlConnection(connstr);
            conn1.Open();
            SqlDataAdapter da1 = new SqlDataAdapter();
            da1.SelectCommand = new SqlCommand("SELECT DISTINCT r.TeamName, r.Score FROM(SELECT TeamName,Score From dbo.Result WHERE Score = 0 AND Date=@date) AS r GROUP BY r.TeamName, r.Score", conn1);

            da1.SelectCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(itemsel));
            SqlDataReader dr1 = da1.SelectCommand.ExecuteReader();
            while (dr1.Read())
            {
                teamName = dr1["TeamName"].ToString();
                score = Convert.ToInt32(dr1["Score"]);

                if (!teamming.Contains(teamName))
                {
                    valueList.Add(new KeyValuePair<string, int>(teamName, score));
                }
            }
            //END HERE
            valueList.Sort(Compare);
            barChart.DataContext = valueList;

        }

        private void Calculate_Click(object sender, RoutedEventArgs e)
        {
            if (comboBox1.SelectedItem != null)
            {
                if (comboBox2.SelectedItem != null)
                {
                    showColumnChart();
                    if (comboBox2.SelectedItem.ToString() == "PieChart")
                    {
                        pieChart.Visibility = Visibility.Visible;
                        pieChart.DataContext = null;
                        pieChart.DataContext = valueList;
                        barChart.Visibility = Visibility.Hidden;
                        columnChart.Visibility = Visibility.Hidden;
                    }
                    else if (comboBox2.SelectedItem.ToString() == "BarChart")
                    {
                        barChart.Visibility = Visibility.Visible;
                        barChart.DataContext = null;
                        barChart.DataContext = valueList;
                        pieChart.Visibility = Visibility.Hidden;
                        columnChart.Visibility = Visibility.Hidden;
                    }
                    else if (comboBox2.SelectedItem.ToString() == "ColumnChart")
                    {
                        columnChart.Visibility = Visibility.Visible;
                        columnChart.DataContext = null;
                        columnChart.DataContext = valueList;
                        barChart.Visibility = Visibility.Hidden;
                        pieChart.Visibility = Visibility.Hidden;
                    }
                    BtnGen.IsEnabled = true;
                }
                else
                {
                    MessageBox.Show("Please Select Type of Chart!", "Warning", MessageBoxButton.OK, MessageBoxImage.Warning);
                }
            }
            else
            {
                MessageBox.Show("Please Select the Date of Event!", "Warning", MessageBoxButton.OK, MessageBoxImage.Warning);
            }
        }

        private void Generate_Click(object sender, RoutedEventArgs e)
        {
            string itemsel = comboBox1.SelectedItem.ToString();

            ArrayList teamnames = new ArrayList();

            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = true;
            Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(1);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];

            worksheet.Cells[1, 1] = "Today's Date:";
            worksheet.Cells[2, 1] = "Date of Quiz:";
            worksheet.Cells[3, 1] = "Day of Event:";

            worksheet.Cells[5, 1] = "Result Information (Team):";
            worksheet.Cells[6, 1] = "Team Name";
            worksheet.Cells[6, 2] = "Total Score";
            //worksheet.Cells[6, 3] = "Total Time";

            worksheet.Cells[6, 1].Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
            worksheet.Cells[6, 2].Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
            worksheet.Cells[6, 3].Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();

            worksheet.Cells[5, 5] = "Result Information (Question):";
            worksheet.Cells[6, 5] = "Question";
            worksheet.Cells[6, 6] = "Number of Teams Answered Correctly";

            worksheet.Cells[6, 5].Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
            worksheet.Cells[6, 6].Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
            worksheet.Cells[6, 7].Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
            worksheet.Cells[6, 8].Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
            worksheet.Cells[6, 9].Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();

            worksheet.Cells[1, 2] = DateTime.Today.ToShortDateString();
            worksheet.Cells[2, 2] = itemsel;
            DateTime dt = Convert.ToDateTime(itemsel);
            worksheet.Cells[3, 2] = dt.ToString("dddd");

            try
            {
                int rowcount = 7;
                int topfive = 0;
                foreach (KeyValuePair<string, int> v in valueList.OrderByDescending(key => key.Value))
                {
                    worksheet.Cells[rowcount, 1] = v.Key;
                    worksheet.Cells[rowcount, 2] = v.Value;

                    if (topfive < 5 && v.Value != 0)
                    {
                        worksheet.Cells[rowcount, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue);
                        worksheet.Cells[rowcount, 2].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue);
                        worksheet.Cells[rowcount, 3].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue);
                    }
                    topfive++;
                    rowcount++;
                }

                //string connstr1 = @"Data Source=.;Initial Catalog=AlgoLogicDatabase;Persist Security Info=True;User ID = sa; Password = imsa";
                string connstr1 = @"Data Source=.;Initial Catalog=AlgoLogicDB;Integrated Security =True";

                SqlConnection conn1 = new SqlConnection(connstr1);
                conn1.Open();
                SqlDataAdapter da1 = new SqlDataAdapter();
                da1.SelectCommand = new SqlCommand("SELECT SUM(Score) As TotalScore, QuizNum From Result Where Date=@date GROUP BY QuizNum", conn1);
                da1.SelectCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(itemsel));

                SqlDataReader dr1 = da1.SelectCommand.ExecuteReader();
                int tscore = 0;
                string qnum = "";
                List<KeyValuePair<string, int>> totalscorelist = new List<KeyValuePair<string, int>>();
                while (dr1.Read())
                {
                    tscore = Convert.ToInt32(dr1["TotalScore"].ToString());
                    qnum = dr1["QuizNum"].ToString();
                    totalscorelist.Add(new KeyValuePair<string, int>(qnum, tscore));
                }

                int rowc = 7;
                foreach (KeyValuePair<string, int> v in totalscorelist)
                {
                    worksheet.Cells[rowc, 5] = v.Key;
                    worksheet.Cells[rowc, 9] = v.Value;
                    worksheet.Cells[rowc, 5].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue);
                    worksheet.Cells[rowc, 6].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue);
                    worksheet.Cells[rowc, 7].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue);
                    worksheet.Cells[rowc, 8].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue);
                    worksheet.Cells[rowc, 9].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue);
                    rowc++;
                }
                //dr1.Close();

                //add totaltime to excelfile
                //    SqlDataAdapter da2 = new SqlDataAdapter();
                //    da2.SelectCommand = new SqlCommand("SELECT SUM(CAST(Time AS INT)) As TotalTime, TeamName From Result Where Date=@date GROUP BY TeamName", conn1);
                //    da2.SelectCommand.Parameters.AddWithValue("@date", Convert.ToDateTime(itemsel));


                //    SqlDataReader dr2 = da2.SelectCommand.ExecuteReader();
                //    int ttime = 0;
                //    string qnum2 = "";
                //    List<KeyValuePair<string, int>> totaltimelist = new List<KeyValuePair<string, int>>();
                //    while (dr2.Read())
                //    {
                //        ttime = Convert.ToInt32(dr2["TotalTime"].ToString());
                //        qnum2 = dr2["TeamName"].ToString();
                //        totaltimelist.Add(new KeyValuePair<string, int>(qnum2, ttime));
                //    }

                //    int rowct = 7;
                //    foreach (KeyValuePair<string, int> v in totaltimelist)
                //    {
                //        worksheet.Cells[rowct, 3] = v.Value;
                //        worksheet.Cells[rowct, 3].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue);
                //        rowct++;
                //    }

                //    dr2.Close();
                conn1.Close();
                app.Quit();
            }
            catch (Exception exx)
            {
                MessageBox.Show(exx.Message);
            }

            this.NavigationService.Refresh();
            barChart.DataContext = null;
            pieChart.DataContext = null;
            columnChart.DataContext = null;
        }

        private void BtnBack_Click(object sender, RoutedEventArgs e)
        {
            NavigationService nav = NavigationService.GetNavigationService(this);
            nav.Navigate(new System.Uri("NavigationPage.xaml", UriKind.RelativeOrAbsolute));
        }

        private void Page_PreviewKeyDown(object sender, KeyEventArgs e)
        {
            if (e.Key == Key.F5 || e.Key == Key.Back)
            {
                e.Handled = true;
            }
        }
    }
}
